/*==============================================================*/
/* DBMS name:      ORACLE Version 11g                           */
/* Created on:     2019/8/22 18:29:46                           */
/*==============================================================*/



-- Type package declaration
create or replace package PDTypes  
as
    TYPE ref_cursor IS REF CURSOR;
end;

-- Integrity package declaration
create or replace package IntegrityPackage AS
 procedure InitNestLevel;
 function GetNestLevel return number;
 procedure NextNestLevel;
 procedure PreviousNestLevel;
 end IntegrityPackage;
/

-- Integrity package definition
create or replace package body IntegrityPackage AS
 NestLevel number;

-- Procedure to initialize the trigger nest level
 procedure InitNestLevel is
 begin
 NestLevel := 0;
 end;


-- Function to return the trigger nest level
 function GetNestLevel return number is
 begin
 if NestLevel is null then
     NestLevel := 0;
 end if;
 return(NestLevel);
 end;

-- Procedure to increase the trigger nest level
 procedure NextNestLevel is
 begin
 if NestLevel is null then
     NestLevel := 0;
 end if;
 NestLevel := NestLevel + 1;
 end;

-- Procedure to decrease the trigger nest level
 procedure PreviousNestLevel is
 begin
 NestLevel := NestLevel - 1;
 end;

 end IntegrityPackage;
/


drop trigger "CompoundDeleteTrigger_depatmen"
/

drop trigger "CompoundInsertTrigger_depatmen"
/

drop trigger "CompoundUpdateTrigger_depatmen"
/

drop trigger "tib_depatment"
/

drop trigger "CompoundDeleteTrigger_employee"
/

drop trigger "CompoundInsertTrigger_employee"
/

drop trigger "CompoundUpdateTrigger_employee"
/

drop trigger "tib_employee"
/

alter table ASSETS
   drop constraint FK_ASSETS_REFERENCE_EMPLOYEE
/

alter table BUY_PLAN
   drop constraint FK_BUY_PLAN_REFERENCE_ASSETS
/

alter table EMPLOYEE
   drop constraint FK_EMPLOYEE_REFERENCE_DEPATMEN
/

alter table EMP_ROLE
   drop constraint FK_EMP_ROLE_REFERENCE_EMPLOYEE
/

alter table EMP_ROLE
   drop constraint FK_EMP_ROLE_REFERENCE_ROLE
/

alter table NOTICE
   drop constraint FK_NOTICE_REFERENCE_EMPLOYEE
/

alter table PATICIPAT
   drop constraint FK_PATICIPA_REFERENCE_EMPLOYEE
/

alter table PATICIPAT
   drop constraint FK_PATICIPA_REFERENCE_PREPARE_
/

alter table PLAN
   drop constraint FK_PLAN_REFERENCE_EMPLOYEE
/

alter table PLAN_AUDIT
   drop constraint FK_PLAN_AUD_REFERENCE_PLAN
/

alter table PREPARE_MEETING
   drop constraint FK_PREPARE__REFERENCE_MEETING_
/

alter table PROJECT
   drop constraint FK_PROJECT_REFERENCE_CUSTOMER
/

alter table PROJECT
   drop constraint FK_PROJECT_REFERENCE_EMPLOYEE
/

alter table ROLE_PRIVILEGE
   drop constraint FK_ROLE_PRI_REFERENCE_PRIVILEG
/

alter table ROLE_PRIVILEGE
   drop constraint FK_ROLE_PRI_REFERENCE_ROLE
/

drop table ASSETS cascade constraints
/

drop table BUY_PLAN cascade constraints
/

drop table CUSTOMER cascade constraints
/

drop table DEPATMENT cascade constraints
/

drop table EMPLOYEE cascade constraints
/

drop table EMP_ROLE cascade constraints
/

drop table MEETING_PLACE cascade constraints
/

drop table NOTICE cascade constraints
/

drop table PATICIPAT cascade constraints
/

drop table PLAN cascade constraints
/

drop table PLAN_AUDIT cascade constraints
/

drop table PREPARE_MEETING cascade constraints
/

drop table PRIVILEGE cascade constraints
/

drop table PROJECT cascade constraints
/

drop table ROLE cascade constraints
/

drop table ROLE_PRIVILEGE cascade constraints
/

drop sequence CUSTOMER_PROJECT_SEQ
/

drop sequence DEPT_ID_SEQ
/

drop sequence EMP_ID_SEQ
/

drop sequence "TITLE_NUMBER-SEQ"
/

create sequence CUSTOMER_PROJECT_SEQ
/

create sequence DEPT_ID_SEQ
start with 800
/

create sequence EMP_ID_SEQ
start with 10000
/

create sequence "TITLE_NUMBER-SEQ"
start with 101
/

/*==============================================================*/
/* Table: ASSETS                                                */
/*==============================================================*/
create table ASSETS 
(
   ASSETS_NO            NUMBER               not null,
   ASSETS_NAME          NVARCHAR2(20)        not null,
   ASSETS_PRICE         NUMBER,
   ASSETS_NUMBER        NUMBER,
   EMP_ID               NUMBER,
   PURCHASING_AGENT     NVARCHAR2(20),
   VERIFIER             NVARCHAR2(20),
   PURCHASE_DATE        DATE,
   DEPRECIATION         NUMBER,
   constraint PK_ASSETS primary key (ASSETS_NO)
)
/

/*==============================================================*/
/* Table: BUY_PLAN                                              */
/*==============================================================*/
create table BUY_PLAN 
(
   AP_NO                NUMBER               not null,
   ASSETS_NO            NUMBER,
   AP_DATE              DATE                 not null,
   EMP_NAME             NVARCHAR2(20)        not null,
   ASSETS_PRICE         NUMBER               not null,
   ASSETS_NUMBER        NUMBER               not null,
   TOTALY_PRICE         NUMBER               not null,
   APPROVAL             CHAR(2)              not null
      constraint CKC_APPROVAL_BUY_PLAN check (APPROVAL in ('1','0')),
   REGISTER             CHAR(2)              not null
      constraint CKC_REGISTER_BUY_PLAN check (REGISTER in ('y','n')),
   constraint PK_BUY_PLAN primary key (AP_NO)
)
/

/*==============================================================*/
/* Table: CUSTOMER                                              */
/*==============================================================*/
create table CUSTOMER 
(
   CUSTOMER_ID          NUMBER               not null,
   CUSTOMER_NAME        NVARCHAR2(20),
   CUSTOMER_POSITION    NVARCHAR2(20),
   CUSTOMER_SEX         CHAR(2)             
      constraint CKC_CUSTOMER_SEX_CUSTOMER check (CUSTOMER_SEX is null or (CUSTOMER_SEX in ('1','0'))),
   CUSTOMER_UNIT        NVARCHAR2(20),
   CUSTOMER_ADDRESS     NVARCHAR2(50),
   CUSTOMER_PHONE       NVARCHAR2(18),
   CUSTOMER_EMAIL       NVARCHAR2(50),
   constraint PK_CUSTOMER primary key (CUSTOMER_ID)
)
/

/*==============================================================*/
/* Table: DEPATMENT                                             */
/*==============================================================*/
create table DEPATMENT 
(
   DEPT_ID              NUMBER               not null,
   DEPT_NAME            NVARCHAR2(20),
   DEPT_INFORMATION     NVARCHAR2(256),
   constraint PK_DEPATMENT primary key (DEPT_ID)
)
/

/*==============================================================*/
/* Table: EMPLOYEE                                              */
/*==============================================================*/
create table EMPLOYEE 
(
   EMP_ID               NUMBER               not null,
   DEPT_ID              NUMBER               not null,
   ROLE_ID              NUMBER,
   EMP_NAME             NVARCHAR2(20)        not null,
   PASSWORD             NVARCHAR2(20)        not null,
   SAL                  NUMBER,
   ID_NUMBER            NVARCHAR2(18),
   ADDRESS              NVARCHAR2(20),
   PHONE_NUMBER         NVARCHAR2(11),
   SEX                  CHAR(2)             
      constraint CKC_SEX_EMPLOYEE check (SEX is null or (SEX in ('1','0'))),
   EMAIL                NVARCHAR2(64),
   EDUCATION            NVARCHAR2(10),
   HIREDATE             DATE,
   constraint PK_EMPLOYEE primary key (EMP_ID)
)
/

/*==============================================================*/
/* Table: EMP_ROLE                                              */
/*==============================================================*/
create table EMP_ROLE 
(
   ROLE_ID              NUMBER               not null,
   EMP_ID               NUMBER               not null,
   constraint PK_EMP_ROLE primary key (ROLE_ID, EMP_ID)
)
/

/*==============================================================*/
/* Table: MEETING_PLACE                                         */
/*==============================================================*/
create table MEETING_PLACE 
(
   MEETING_PLACE        NVARCHAR2(20)        not null,
   GALLEYFUL            NUMBER               not null,
   ALLOCATE_RESOURCE    NVARCHAR2(20)        not null,
   constraint PK_MEETING_PLACE primary key (MEETING_PLACE)
)
/

/*==============================================================*/
/* Table: NOTICE                                                */
/*==============================================================*/
create table NOTICE 
(
   NOTICE_TITLE         NVARCHAR2(40)        not null,
   NOTICE_DATE          DATE                 not null,
   EMP_NAME             NVARCHAR2(20)        not null,
   EMP_ID               NUMBER               not null,
   NOTICE_CONTENT       NVARCHAR2(256)       not null,
   constraint PK_NOTICE primary key (NOTICE_TITLE)
)
/

/*==============================================================*/
/* Table: PATICIPAT                                             */
/*==============================================================*/
create table PATICIPAT 
(
   EMP_ID               NUMBER               not null,
   MEETING_ID           NUMBER               not null,
   CONFERENCE_THEME     NVARCHAR2(256)       not null,
   PRESENT_SITUATION    CHAR(2)             
      constraint CKC_PRESENT_SITUATION_PATICIPA check (PRESENT_SITUATION is null or (PRESENT_SITUATION in ('1','0'))),
   constraint PK_PATICIPAT primary key (EMP_ID)
)
/

/*==============================================================*/
/* Table: PLAN                                                  */
/*==============================================================*/
create table PLAN 
(
   PLAN_ID              NVARCHAR2(20)        not null,
   PLAN_THEME           NVARCHAR2(20)        not null,
   MEETING_THEME        NVARCHAR2(30)        not null,
   DEVELOP_CONTENT      NVARCHAR2(256)       not null,
   NOTICE_CONTENT       NVARCHAR2(256)       not null,
   PLAN_TYPE            VARCHAR2(20)         not null,
   P_START              DATE                 not null,
   P_FINISH             DATE                 not null,
   PLAN_LEADER          NVARCHAR2(20)        not null,
   EMP_ID               NUMBER               not null,
   constraint PK_PLAN primary key (PLAN_ID)
)
/

/*==============================================================*/
/* Table: PLAN_AUDIT                                            */
/*==============================================================*/
create table PLAN_AUDIT 
(
   PLAN_ID              NVARCHAR2(20)        not null,
   F_AD                 NVARCHAR2(20)        not null,
   FIN_AUDIT_RES        CHAR(3)             
      constraint CKC_FIN_AUDIT_RES_PLAN_AUD check (FIN_AUDIT_RES is null or (FIN_AUDIT_RES in ('1','0'))),
   M_AD                 NVARCHAR2(20)        not null,
   MAR_AUDIT_RES        CHAR(3)             
      constraint CKC_MAR_AUDIT_RES_PLAN_AUD check (MAR_AUDIT_RES is null or (MAR_AUDIT_RES in ('1','0'))),
   constraint PK_PLAN_AUDIT primary key (PLAN_ID)
)
/

/*==============================================================*/
/* Table: PREPARE_MEETING                                       */
/*==============================================================*/
create table PREPARE_MEETING 
(
   MEETING_ID           NUMBER               not null,
   SPEAKER              NVARCHAR2(20)        not null,
   MEETING_FORMAT       NVARCHAR2(30)        not null,
   MEETING_THEME        NVARCHAR2(30)        not null,
   START_TIME           DATE,
   END_TIME             DATE,
   MEETING_PLACE        NVARCHAR2(20),
   MEETING_CONTENT      NVARCHAR2(256)       not null,
   constraint PK_PREPARE_MEETING primary key (MEETING_ID)
)
/

/*==============================================================*/
/* Table: PRIVILEGE                                             */
/*==============================================================*/
create table PRIVILEGE 
(
   PRIVILEGE_ID         NUMBER               not null,
   PRIVILEGE_NAME       NVARCHAR2(20)        not null,
   PRIVILEGE_DESCRIPTION NVARCHAR2(255),
   constraint PK_PRIVILEGE primary key (PRIVILEGE_ID)
)
/

/*==============================================================*/
/* Table: PROJECT                                               */
/*==============================================================*/
create table PROJECT 
(
   PROJECT_ID           NUMBER               not null,
   PROJECT_NAME         NVARCHAR2(20),
   PROJECT_AMOUNT       NUMBER(9,2),
   PROJECT_FURATION     DATE,
   PROJECT_LEADER_ID    NUMBER,
   CUSTOMER_ID          NUMBER,
   PROJECT_LEADER_NAME  NVARCHAR2(20)        not null,
   constraint PK_PROJECT primary key (PROJECT_ID)
)
/

/*==============================================================*/
/* Table: ROLE                                                  */
/*==============================================================*/
create table ROLE 
(
   ROLE_ID              NUMBER               not null,
   ROLO_NAME            NVARCHAR2(20)        not null,
   ROLE_DESCRIPTION     NVARCHAR2(255),
   constraint PK_ROLE primary key (ROLE_ID)
)
/

/*==============================================================*/
/* Table: ROLE_PRIVILEGE                                        */
/*==============================================================*/
create table ROLE_PRIVILEGE 
(
   ROLE_ID              NUMBER               not null,
   PRIVILEGE            NUMBER               not null,
   constraint PK_ROLE_PRIVILEGE primary key (ROLE_ID, PRIVILEGE)
)
/

alter table ASSETS
   add constraint FK_ASSETS_REFERENCE_EMPLOYEE foreign key (EMP_ID)
      references EMPLOYEE (EMP_ID)
/

alter table BUY_PLAN
   add constraint FK_BUY_PLAN_REFERENCE_ASSETS foreign key (ASSETS_NO)
      references ASSETS (ASSETS_NO)
/

alter table EMPLOYEE
   add constraint FK_EMPLOYEE_REFERENCE_DEPATMEN foreign key (DEPT_ID)
      references DEPATMENT (DEPT_ID)
/

alter table EMP_ROLE
   add constraint FK_EMP_ROLE_REFERENCE_EMPLOYEE foreign key (EMP_ID)
      references EMPLOYEE (EMP_ID)
/

alter table EMP_ROLE
   add constraint FK_EMP_ROLE_REFERENCE_ROLE foreign key (ROLE_ID)
      references ROLE (ROLE_ID)
/

alter table NOTICE
   add constraint FK_NOTICE_REFERENCE_EMPLOYEE foreign key (EMP_ID)
      references EMPLOYEE (EMP_ID)
/

alter table PATICIPAT
   add constraint FK_PATICIPA_REFERENCE_EMPLOYEE foreign key (EMP_ID)
      references EMPLOYEE (EMP_ID)
/

alter table PATICIPAT
   add constraint FK_PATICIPA_REFERENCE_PREPARE_ foreign key (MEETING_ID)
      references PREPARE_MEETING (MEETING_ID)
/

alter table PLAN
   add constraint FK_PLAN_REFERENCE_EMPLOYEE foreign key (EMP_ID)
      references EMPLOYEE (EMP_ID)
/

alter table PLAN_AUDIT
   add constraint FK_PLAN_AUD_REFERENCE_PLAN foreign key (PLAN_ID)
      references PLAN (PLAN_ID)
/

alter table PREPARE_MEETING
   add constraint FK_PREPARE__REFERENCE_MEETING_ foreign key (MEETING_PLACE)
      references MEETING_PLACE (MEETING_PLACE)
/

alter table PROJECT
   add constraint FK_PROJECT_REFERENCE_CUSTOMER foreign key (CUSTOMER_ID)
      references CUSTOMER (CUSTOMER_ID)
/

alter table PROJECT
   add constraint FK_PROJECT_REFERENCE_EMPLOYEE foreign key (PROJECT_LEADER_ID)
      references EMPLOYEE (EMP_ID)
/

alter table ROLE_PRIVILEGE
   add constraint FK_ROLE_PRI_REFERENCE_PRIVILEG foreign key (PRIVILEGE)
      references PRIVILEGE (PRIVILEGE_ID)
/

alter table ROLE_PRIVILEGE
   add constraint FK_ROLE_PRI_REFERENCE_ROLE foreign key (ROLE_ID)
      references ROLE (ROLE_ID)
/


create or replace trigger "CompoundDeleteTrigger_depatmen"
for delete on DEPATMENT compound trigger
// Declaration
// Body
  before statement is
  begin
     NULL;
  end before statement;

  before each row is
  begin
     NULL;
  end before each row;

  after each row is
  begin
     NULL;
  end after each row;

  after statement is
  begin
     NULL;
  end after statement;

END
/


create or replace trigger "CompoundInsertTrigger_depatmen"
for insert on DEPATMENT compound trigger
// Declaration
// Body
  before statement is
  begin
     NULL;
  end before statement;

  before each row is
  begin
     NULL;
  end before each row;

  after each row is
  begin
     NULL;
  end after each row;

  after statement is
  begin
     NULL;
  end after statement;

END
/


create or replace trigger "CompoundUpdateTrigger_depatmen"
for update on DEPATMENT compound trigger
// Declaration
// Body
  before statement is
  begin
     NULL;
  end before statement;

  before each row is
  begin
     NULL;
  end before each row;

  after each row is
  begin
     NULL;
  end after each row;

  after statement is
  begin
     NULL;
  end after statement;

END
/


create trigger "tib_depatment" before insert
on DEPATMENT for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "DEPT_ID" uses sequence DEPT_ID_SEQ
    select DEPT_ID_SEQ.NEXTVAL INTO :new.DEPT_ID from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create or replace trigger "CompoundDeleteTrigger_employee"
for delete on EMPLOYEE compound trigger
// Declaration
// Body
  before statement is
  begin
     NULL;
  end before statement;

  before each row is
  begin
     NULL;
  end before each row;

  after each row is
  begin
     NULL;
  end after each row;

  after statement is
  begin
     NULL;
  end after statement;

END
/


create or replace trigger "CompoundInsertTrigger_employee"
for insert on EMPLOYEE compound trigger
// Declaration
// Body
  before statement is
  begin
     NULL;
  end before statement;

  before each row is
  begin
     NULL;
  end before each row;

  after each row is
  begin
     NULL;
  end after each row;

  after statement is
  begin
     NULL;
  end after statement;

END
/


create or replace trigger "CompoundUpdateTrigger_employee"
for update on EMPLOYEE compound trigger
// Declaration
// Body
  before statement is
  begin
     NULL;
  end before statement;

  before each row is
  begin
     NULL;
  end before each row;

  after each row is
  begin
     NULL;
  end after each row;

  after statement is
  begin
     NULL;
  end after statement;

END
/


create trigger "tib_employee" before insert
on EMPLOYEE for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "EMP_ID" uses sequence EMP_ID_SEQ
    select EMP_ID_SEQ.NEXTVAL INTO :new.EMP_ID from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/

